package com.homework.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.homework.model.Homework;
import com.homework.model.Studentexamscore;

public class StudentexamscoreJdbc {
	JdbcUtil jdbc_util = new JdbcUtil();
	HomeworkJdbc homeworkJdbc = new HomeworkJdbc();

	public void insert(Studentexamscore a) {
		jdbc_util.Connect();
		String sql = "INSERT INTO Studentexamscore (id,studentid,homeworkid) VALUES (?,?,?)";
		try {
			jdbc_util.stmt = JdbcUtil.conn.prepareStatement(sql);
			jdbc_util.stmt.setString(1, a.getId());
			jdbc_util.stmt.setString(2, a.getStudentid());
			jdbc_util.stmt.setString(3, a.getHomeworkid());
			jdbc_util.stmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		jdbc_util.Close();

	}

	public void update(Studentexamscore cla) {
		jdbc_util.Connect();
		String sql = "UPDATE Studentexamscore SET score=? WHERE id = ?";
		try {
			jdbc_util.stmt = jdbc_util.conn.prepareStatement(sql);
			jdbc_util.stmt.setString(1, cla.getScore());
			jdbc_util.stmt.setString(2, cla.getId());
			jdbc_util.stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		jdbc_util.Close();
	}

	public Studentexamscore selectById(Studentexamscore cla) {
		jdbc_util.Connect();
		String sql = "select * from Studentexamscore where id=?";
		Studentexamscore a = null;
		try {
			jdbc_util.stmt = JdbcUtil.conn.prepareStatement(sql);
			jdbc_util.stmt.setString(1, cla.getId());
			ResultSet rs = jdbc_util.stmt.executeQuery();
			while (rs.next()) {
				a = new Studentexamscore();
				a.setAdvice(rs.getString("advice"));
				a.setHomeworkid(rs.getString("homeworkid"));
				a.setId(rs.getString("id"));
				a.setScore(rs.getString("score"));
				a.setStudentid(rs.getString("studentid"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		jdbc_util.Close();
		return a;
	}

	public List<Studentexamscore> selectByStudentId(Studentexamscore cla) {
		jdbc_util.Connect();
		String sql = "select * from Studentexamscore where studentid=?";
		List<Studentexamscore> list = new ArrayList<>();
		Studentexamscore a = null;
		try {
			jdbc_util.stmt = JdbcUtil.conn.prepareStatement(sql);
			jdbc_util.stmt.setString(1, cla.getStudentid());
			ResultSet rs = jdbc_util.stmt.executeQuery();
			while (rs.next()) {
				a = new Studentexamscore();
				a.setAdvice(rs.getString("advice"));
				a.setHomeworkid(rs.getString("homeworkid"));
				a.setId(rs.getString("id"));
				a.setScore(rs.getString("score"));
				a.setStudentid(rs.getString("studentid"));
				list.add(a);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		jdbc_util.Close();
		for (Studentexamscore s : list) {
			Homework selectById = homeworkJdbc.selectById(new Homework(s.getHomeworkid()));
			s.setHomework(selectById);
		}
		return list;
	}

}
